<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Conditional Functions And Expressions | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-functions-conditional.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-conditional.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-conditional.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-functions-conditional.html" rel="nofollow" target="_blank">../en/sql-functions-conditional.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-functions.html">Functions and Operators</a></span>
»
<span class="breadcrumb-node">Conditional Functions And Expressions</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-functions-geo.html">« Geo Functions</a>
</span>
<span class="next">
<a href="sql-functions-system.html">System Functions »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-functions-conditional"></a>Conditional Functions And Expressions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p>Functions that return one of their arguments by evaluating in an if-else manner.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-case"></a><code class="literal">CASE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CASE WHEN condition THEN result
    [WHEN ...]
    [ELSE default_result]
END</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<p>One or multiple <em>WHEN <span class="strong strong"><strong>condition</strong></span> THEN <span class="strong strong"><strong>result</strong></span></em> clauses are used and the expression can optionally have
an <em>ELSE <span class="strong strong"><strong>default_result</strong></span></em> clause. Every <span class="strong strong"><strong>condition</strong></span> should be a boolean expression.</p>
<p><span class="strong strong"><strong>Output</strong></span>: one of the <span class="strong strong"><strong>result</strong></span> expressions if the corresponding <em>WHEN <span class="strong strong"><strong>condition</strong></span></em> evaluates to <code class="literal">true</code> or
the <span class="strong strong"><strong>default_result</strong></span> if all <em>WHEN <span class="strong strong"><strong>condition</strong></span></em> clauses evaluate to <code class="literal">false</code>. If the optional <em>ELSE <span class="strong strong"><strong>default_result</strong></span></em>
clause is missing and all <em>WHEN <span class="strong strong"><strong>condition</strong></span></em> clauses evaluate to <code class="literal">false</code> then <code class="literal">null</code> is returned.</p>
<p><span class="strong strong"><strong>Description</strong></span>: The CASE expression is a generic conditional expression which simulates if/else statements of other programming languages
If the condition’s result is true, the value of the result expression that follows the condition will be the returned
the subsequent when clauses will be skipped and not processed.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CASE WHEN 1 &gt; 2 THEN 'elastic'
            WHEN 2 &lt;= 3 THEN 'search'
       END AS "case";

    case
---------------
search</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CASE WHEN 1 &gt; 2 THEN 'elastic'
            WHEN 2 &gt; 10 THEN 'search'
       END AS "case";

    case
---------------
null</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CASE WHEN 1 &gt; 2 THEN 'elastic'
            WHEN 2 &gt; 10 THEN 'search'
            ELSE 'default'
       END AS "case";

    case
---------------
default</pre>
</div>
<p>As a variant, a case expression can be expressed with a syntax similar to <span class="strong strong"><strong>switch-case</strong></span> of other programming languages:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CASE expression
     WHEN value1 THEN result1
    [WHEN value2 THEN result2]
    [WHEN ...]
    [ELSE default_result]
END</pre>
</div>
<p>In this case it’s transformed internally to:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CASE WHEN expression = value1 THEN result1
    [WHEN expression = value2 THEN result2]
    [WHEN ...]
    [ELSE default_result]
END</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CASE 5
            WHEN 1 THEN 'elastic'
            WHEN 2 THEN 'search'
            WHEN 5 THEN 'elasticsearch'
       END AS "case";

    case
---------------
elasticsearch</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT CASE 5
            WHEN 1 THEN 'elastic'
            WHEN 2 THEN 'search'
            WHEN 3 THEN 'elasticsearch'
            ELSE 'default'
       END AS "case";

    case
---------------
default</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>All result expressions must be of compatible data types. More specifically all result
expressions should have a compatible data type with the 1st <em>non-null</em> result expression.
E.g.:</p>
<p>for the following query:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">CASE WHEN a = 1 THEN null
     WHEN a &gt; 2 THEN 10
     WHEN a &gt; 5 THEN 'foo'
END</pre>
</div>
<p>an error message would be returned, mentioning that <span class="strong strong"><strong><em>foo</em></strong></span> is of data type <span class="strong strong"><strong>keyword</strong></span>,
which does not match the expected data type <span class="strong strong"><strong>integer</strong></span> (based on result <span class="strong strong"><strong>10</strong></span>).</p>
</div>
</div>
<div class="section">
<div class="titlepage"><div><div>
<h4 class="title">
<a id="sql-functions-conditional-case-groupby-custom-buckets"></a>Conditional bucketing<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h4>
</div></div></div>
<p>CASE can be used as a GROUP BY key in a query to facilitate custom bucketing
and assign descriptive names to those buckets. If, for example, the values
for a key are too many or, simply, ranges of those values are more
interesting than every single value, CASE can create custom buckets as in the
following example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT count(*) AS count,
  CASE WHEN NVL(languages, 0) = 0 THEN 'zero'
    WHEN languages = 1 THEN 'one'
    WHEN languages = 2 THEN 'bilingual'
    WHEN languages = 3 THEN 'trilingual'
    ELSE 'multilingual'
  END as lang_skills
FROM employees
GROUP BY lang_skills
ORDER BY lang_skills;</pre>
</div>
<p>With this query, one can create normal grouping buckets for values <em>0, 1, 2, 3</em> with
descriptive names, and every value <em>&gt;= 4</em> falls into the <em>multilingual</em> bucket.</p>
</div>

</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-coalesce"></a><code class="literal">COALESCE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">COALESCE(
    expression, <a id="CO171-1"></a><i class="conum" data-value="1"></i>
    expression, <a id="CO171-2"></a><i class="conum" data-value="2"></i>
    ...)</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO171-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO171-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p>…​</p>
<p><span class="strong strong"><strong>N</strong></span>th expression</p>
<p>COALESCE can take an arbitrary number of arguments.</p>
<p><span class="strong strong"><strong>Output</strong></span>: one of the expressions or <code class="literal">null</code></p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the first of its arguments that is not null.
If all arguments are null, then it returns <code class="literal">null</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT COALESCE(null, 'elastic', 'search') AS "coalesce";

    coalesce
---------------
elastic</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT COALESCE(null, null, null, null) AS "coalesce";

    coalesce
---------------
null</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-greatest"></a><code class="literal">GREATEST</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">GREATEST(
    expression, <a id="CO172-1"></a><i class="conum" data-value="1"></i>
    expression, <a id="CO172-2"></a><i class="conum" data-value="2"></i>
    ...)</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO172-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO172-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p>…​</p>
<p><span class="strong strong"><strong>N</strong></span>th expression</p>
<p>GREATEST can take an arbitrary number of arguments and
all of them must be of the same data type.</p>
<p><span class="strong strong"><strong>Output</strong></span>: one of the expressions or <code class="literal">null</code></p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the argument that has the largest value which is not null.
If all arguments are null, then it returns <code class="literal">null</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT GREATEST(null, 1, 2) AS "greatest";

    greatest
---------------
2</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT GREATEST(null, null, null, null) AS "greatest";

    greatest
---------------
null</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-ifnull"></a><code class="literal">IFNULL</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">IFNULL(
    expression, <a id="CO173-1"></a><i class="conum" data-value="1"></i>
    expression) <a id="CO173-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO173-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO173-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: 2nd expression if 1st expression is null, otherwise 1st expression.</p>
<p><span class="strong strong"><strong>Description</strong></span>: Variant of <a class="xref" href="sql-functions-conditional.html#sql-functions-conditional-coalesce" title="COALESCE"><code class="literal">COALESCE</code></a> with only two arguments.
Returns the first of its arguments that is not null.
If all arguments are null, then it returns <code class="literal">null</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT IFNULL('elastic', null) AS "ifnull";

    ifnull
---------------
elastic</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT IFNULL(null, 'search') AS "ifnull";

    ifnull
---------------
search</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-iif"></a><code class="literal">IIF</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">IIF(expression,   <a id="CO174-1"></a><i class="conum" data-value="1"></i>
    expression,   <a id="CO174-2"></a><i class="conum" data-value="2"></i>
    [expression]) <a id="CO174-3"></a><i class="conum" data-value="3"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO174-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>boolean condition to check</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO174-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>return value if the boolean condition evaluates to <code class="literal">true</code></p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO174-3"><i class="conum" data-value="3"></i></a></p>
</td>
<td align="left" valign="top">
<p>return value if the boolean condition evaluates <code class="literal">false</code>; optional</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: 2nd expression if 1st expression (condition) evaluates to <code class="literal">true</code>. If it evaluates to <code class="literal">false</code>
return 3rd expression. If 3rd expression is not provided return <code class="literal">null</code>.</p>
<p><span class="strong strong"><strong>Description</strong></span>: Conditional function that implements the standard <em>IF &lt;condition&gt; THEN &lt;result1&gt; ELSE &lt;result2&gt;</em>
logic of programming languages. If the 3rd expression is not provided and the condition evaluates to <code class="literal">false</code>,
<code class="literal">null</code> is returned.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT IIF(1 &lt; 2, 'TRUE', 'FALSE') AS result1, IIF(1 &gt; 2, 'TRUE', 'FALSE') AS result2;

    result1    |    result2
---------------+---------------
TRUE           |FALSE</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT IIF(1 &lt; 2, 'TRUE') AS result1, IIF(1 &gt; 2 , 'TRUE') AS result2;

    result1    |    result2
---------------+---------------
TRUE           |null</pre>
</div>
<div class="tip admon">
<div class="icon"></div>
<div class="admon_content">
<p><span class="strong strong"><strong>IIF</strong></span> functions can be combined to implement more complex logic simulating the <a class="xref" href="sql-functions-conditional.html#sql-functions-conditional-case" title="CASE"><code class="literal">CASE</code></a>
expression. E.g.:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">IIF(a = 1, 'one', IIF(a = 2, 'two', IIF(a = 3, 'three', 'many')))</pre>
</div>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-isnull"></a><code class="literal">ISNULL</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">ISNULL(
    expression, <a id="CO175-1"></a><i class="conum" data-value="1"></i>
    expression) <a id="CO175-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO175-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO175-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: 2nd expression if 1st expression is null, otherwise 1st expression.</p>
<p><span class="strong strong"><strong>Description</strong></span>: Variant of <a class="xref" href="sql-functions-conditional.html#sql-functions-conditional-coalesce" title="COALESCE"><code class="literal">COALESCE</code></a> with only two arguments.
Returns the first of its arguments that is not null.
If all arguments are null, then it returns <code class="literal">null</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT ISNULL('elastic', null) AS "isnull";

    isnull
---------------
elastic</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT ISNULL(null, 'search') AS "isnull";

    isnull
---------------
search</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-least"></a><code class="literal">LEAST</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">LEAST(
    expression, <a id="CO176-1"></a><i class="conum" data-value="1"></i>
    expression, <a id="CO176-2"></a><i class="conum" data-value="2"></i>
    ...)</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO176-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO176-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p>…​</p>
<p><span class="strong strong"><strong>N</strong></span>th expression</p>
<p>LEAST can take an arbitrary number of arguments and
all of them must be of the same data type.</p>
<p><span class="strong strong"><strong>Output</strong></span>: one of the expressions or <code class="literal">null</code></p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the argument that has the smallest value which is not null.
If all arguments are null, then it returns <code class="literal">null</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT LEAST(null, 2, 1) AS "least";

    least
---------------
1</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT LEAST(null, null, null, null) AS "least";

    least
---------------
null</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-nullif"></a><code class="literal">NULLIF</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">NULLIF(
    expression, <a id="CO177-1"></a><i class="conum" data-value="1"></i>
    expression) <a id="CO177-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO177-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO177-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">null</code> if the 2 expressions are equal, otherwise the 1st expression.</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns <code class="literal">null</code> when the two input expressions are equal and
if not, it returns the 1st expression.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT NULLIF('elastic', 'search') AS "nullif";
    nullif
---------------
elastic</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT NULLIF('elastic', 'elastic') AS "nullif";

    nullif:s
---------------
null</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-conditional-nvl"></a><code class="literal">NVL</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/conditional.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">NVL(
    expression, <a id="CO178-1"></a><i class="conum" data-value="1"></i>
    expression) <a id="CO178-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO178-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>1st expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO178-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>2nd expression</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: 2nd expression if 1st expression is null, otherwise 1st expression.</p>
<p><span class="strong strong"><strong>Description</strong></span>: Variant of <a class="xref" href="sql-functions-conditional.html#sql-functions-conditional-coalesce" title="COALESCE"><code class="literal">COALESCE</code></a> with only two arguments.
Returns the first of its arguments that is not null.
If all arguments are null, then it returns <code class="literal">null</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT NVL('elastic', null) AS "nvl";

    nvl
---------------
elastic</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT NVL(null, 'search') AS "nvl";

    nvl
---------------
search</pre>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-functions-geo.html">« Geo Functions</a>
</span>
<span class="next">
<a href="sql-functions-system.html">System Functions »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>